package com.sduept.utils;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import com.sduept.nwld.dataserver.controller.base.secondary.ProtectionEquipmentDTO;

/**
 * Excel导出，目前支持多sheet页、支持合并前两行
 * @author Ningfw
 * @date 2018-01-18
 *
 */
public class ExcelUtils {

	/**
	 * 导出Excel
	 * @param commentType sheet页
	 * @param headerList 表头
	 * @param fieldList 内容（表头所对应的entity中的属性），顺序和表头顺序一致
	 * @param mergeList 需要合并单元格的属性名（对应entity中的属性名）注：目前只支持合并前两行
	 * @param filePath 文件路径
	 * @param fileName 文件名
	 * @param fileType 文件类型
	 * 
	 * @return 文件存储状态
	 */
	public static boolean exportExcel(Map<String, List<ProtectionEquipmentDTO>> commentType, List<String> headerList, 
			List<String> fieldList, List<String> mergeList, String filePath, String fileName, String fileType){
			
			boolean result = true;
	        HSSFWorkbook workbook = new HSSFWorkbook();  
	        
	        for(Map.Entry<String, List<ProtectionEquipmentDTO>> entry : commentType.entrySet()){
	        	
	            HSSFSheet sheet = workbook.createSheet(entry.getKey());//在webbook中添加sheet
	            HSSFRow row = sheet.createRow((int) 0);//在sheet中添加表头第0行
	            HSSFCellStyle style = workbook.createCellStyle();//创建单元格  
	            style.setAlignment(HorizontalAlignment.CENTER); //创建一个居中格式 
	            style.setVerticalAlignment(VerticalAlignment.CENTER);
	            HSSFFont font = workbook.createFont();
	            font.setBold(true);//粗体
	            style.setFont(font);
	            HSSFCellStyle centerStyle = workbook.createCellStyle();//样式2：水平/垂直居中
	            centerStyle.setAlignment(HorizontalAlignment.CENTER);
	            centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//	            HSSFCell cell = row.createCell(0);  
//	            cell.setCellValue("保护设备");  
//	            cell.setCellStyle(style);  
//	            cell = row.createCell(1);  
//	            cell.setCellValue("保护名称"); 
//	            cell = row.createCell(2);  
//	            cell.setCellValue("类型"); 
	            for(int i = 0; i < headerList.size(); i++){
	            	
	            	HSSFCell cell = row.createCell(i);
	            	cell.setCellValue(headerList.get(i));
	            	cell.setCellStyle(style);
	            }
	      
	            //写入实体数据
	            List<ProtectionEquipmentDTO> protList = entry.getValue();
	            int commonNum = 0;//行相同个数
	            int commonNum2 = 0;
	            for (int i = 0; i < protList.size(); i++){
	            	
	                row = sheet.createRow((int) i + 1);  
	                ProtectionEquipmentDTO prot = protList.get(i);
//	                row.createCell(0).setCellValue(prot.getProtectedName());
//	                row.createCell(1).setCellValue(prot.getName());
//	                row.createCell(2).setCellValue(prot.getType());
	                
	                for(int j = 0; j < fieldList.size(); j++){//通过属性名，用反射得到get方法并获取执行值
	                	
	                	Object obj = obtainGetVal(ProtectionEquipmentDTO.class, fieldList.get(j), prot);//执行get方法返回一个Object
		            	row.createCell(j).setCellValue(obj==null ? "" : obj.toString());//创建单元格，并设置值 
		            	row.getCell(j).setCellStyle(centerStyle);
	                }
	                
	                if(mergeList.size() > 0){//需要合并单元格
	                	
	                	if(i > 0){//从第二行开始合并
	                    	
	                		String firstMerge = mergeList.get(0);
	                    	if(obtainGetVal(ProtectionEquipmentDTO.class, firstMerge, protList.get(i)).toString()
	                			.equals(obtainGetVal(ProtectionEquipmentDTO.class, firstMerge, protList.get(i-1)).toString())){
	                    		
	                    		commonNum++;
	                    		if(mergeList.size() >= 2){
	                    			
	                    			String secondMerge = mergeList.get(1);
	                    			if(obtainGetVal(ProtectionEquipmentDTO.class, secondMerge, protList.get(i))!=null && 
	                					obtainGetVal(ProtectionEquipmentDTO.class, secondMerge, protList.get(i)).toString()
	                        			.equals(obtainGetVal(ProtectionEquipmentDTO.class, secondMerge, protList.get(i-1)).toString())){
	                        			commonNum2++;
	                            	}else{
	                            		
	                            		if(commonNum2 > 0){//相同单元格数为不为0时进行合并
	                            			sheet.addMergedRegion(new CellRangeAddress(i-commonNum2, i, 1, 1));//合并单元格
	                            			commonNum2 = 0;
	                            		}
	                            	}
	                    		}
	                    	}else{
	                    		if(commonNum > 0){//相同单元格数为不为0时进行合并

	                    			//first row(0-based) last row(0-based) first column(0-based) last column(0-based)
	                    			sheet.addMergedRegion(new CellRangeAddress(i-commonNum, i, 0, 0));//合并单元格
	                    			commonNum = 0;
	                    			if(commonNum2 > 0){//相同单元格数为不为0时进行合并
	                        			sheet.addMergedRegion(new CellRangeAddress(i-commonNum2, i, 1, 1));//合并单元格
	                        			commonNum2 = 0;
	                        		}
	                    		}
	                    	}
	                    	if((i == (protList.size() - 1)) && (commonNum > 0)){//最后一行且相同单元格数为不为0时进行合并
	                    		
	                    		sheet.addMergedRegion(new CellRangeAddress((i+1)-commonNum, i+1, 0, 0));//合并单元格
	                    		if(commonNum2 > 0){//相同单元格数为不为0时进行合并
	                    			sheet.addMergedRegion(new CellRangeAddress((i+1)-commonNum2, i+1, 1, 1));//合并单元格
	                    			commonNum2 = 0;
	                    		}
	                    	}
	                    }
	                }
	            }  
	        }
	        
	        try{
	        	
	            FileOutputStream fout = new FileOutputStream(filePath + fileName + fileType);//存储
	            workbook.write(fout);  
	            fout.close();
	        }catch (Exception e) {
	        	
	        	result = false;
	            e.printStackTrace();
	        }finally{
	        	
	        	try {
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
	        }
	        
	        return result;
		}
	
	/**
	 * 导出Excel
	 * @param commentType sheet页
	 * @param headerList 表头
	 * @param fieldList 内容（表头所对应的entity中的属性），顺序和表头顺序一致
	 * @param mergeList 需要合并单元格的属性名（对应entity中的属性名）注：目前只支持合并前两行
	 * @param filePath 文件路径
	 * @param fileName 文件名
	 * @param fileType 文件类型
	 * 
	 * @return 文件存储路径
	 */
	public static String exportExcelReturnPath(Map<String, List<ProtectionEquipmentDTO>> commentType, List<String> headerList, 
			List<String> fieldList, List<String> mergeList, String filePath, String fileName, String fileType){
			
			String storagePath = null;
	        HSSFWorkbook workbook = new HSSFWorkbook();  
	        
	        for(Map.Entry<String, List<ProtectionEquipmentDTO>> entry : commentType.entrySet()){
	        	
	            HSSFSheet sheet = workbook.createSheet(entry.getKey());//在webbook中添加sheet
	            HSSFRow row = sheet.createRow((int) 0);//在sheet中添加表头第0行
	            HSSFCellStyle style = workbook.createCellStyle();//创建单元格  
	            style.setAlignment(HorizontalAlignment.CENTER); //创建一个居中格式 
	            style.setVerticalAlignment(VerticalAlignment.CENTER);
	            HSSFFont font = workbook.createFont();
	            font.setBold(true);//粗体
	            style.setFont(font);
	            HSSFCellStyle centerStyle = workbook.createCellStyle();//样式2：水平/垂直居中
	            centerStyle.setAlignment(HorizontalAlignment.CENTER);
	            centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//	            HSSFCell cell = row.createCell(0);  
//	            cell.setCellValue("保护设备");  
//	            cell.setCellStyle(style);  
//	            cell = row.createCell(1);  
//	            cell.setCellValue("保护名称"); 
//	            cell = row.createCell(2);  
//	            cell.setCellValue("类型"); 
	            for(int i = 0; i < headerList.size(); i++){
	            	
	            	HSSFCell cell = row.createCell(i);
	            	cell.setCellValue(headerList.get(i));
	            	cell.setCellStyle(style);
	            }
	      
	            //写入实体数据
	            List<ProtectionEquipmentDTO> protList = entry.getValue();
	            int commonNum = 0;//行相同个数
	            int commonNum2 = 0;
	            for (int i = 0; i < protList.size(); i++){
	            	
	                row = sheet.createRow((int) i + 1);  
	                ProtectionEquipmentDTO prot = protList.get(i);
//	                row.createCell(0).setCellValue(prot.getProtectedName());
//	                row.createCell(1).setCellValue(prot.getName());
//	                row.createCell(2).setCellValue(prot.getType());
	                
	                for(int j = 0; j < fieldList.size(); j++){//通过属性名，用反射得到get方法并获取执行值
	                	
	                	Object obj = obtainGetVal(ProtectionEquipmentDTO.class, fieldList.get(j), prot);//执行get方法返回一个Object
		            	row.createCell(j).setCellValue(obj==null ? "" : obj.toString());//创建单元格，并设置值 
		            	row.getCell(j).setCellStyle(centerStyle);
	                }
	                
	                if(mergeList.size() > 0){//需要合并单元格
	                	
	                	if(i > 0){//从第二行开始合并
	                    	
	                		String firstMerge = mergeList.get(0);
	                		Object firstObj = obtainGetVal(ProtectionEquipmentDTO.class, firstMerge, protList.get(i));
	                		Object firstObjMinus = obtainGetVal(ProtectionEquipmentDTO.class, firstMerge, protList.get(i-1));
	                    	if(firstObj != null && firstObj.toString().equals(firstObjMinus == null ? null : firstObjMinus.toString())){
	                    		
	                    		commonNum++;
	                    		if(mergeList.size() >= 2){
	                    			
	                    			String secondMerge = mergeList.get(1);
	                    			Object secondObj = obtainGetVal(ProtectionEquipmentDTO.class, secondMerge, protList.get(i));
	                    			Object secondObjMinus = obtainGetVal(ProtectionEquipmentDTO.class, secondMerge, protList.get(i-1));
	                    			if(secondObj != null && secondObj.toString().equals(secondObjMinus == null ? null : secondObjMinus.toString())){
	                        			commonNum2++;
	                            	}else{
	                            		
	                            		if(commonNum2 > 0){//相同单元格数为不为0时进行合并
	                            			sheet.addMergedRegion(new CellRangeAddress(i-commonNum2, i, 1, 1));//合并单元格
	                            			commonNum2 = 0;
	                            		}
	                            	}
	                    		}
	                    	}else{
	                    		if(commonNum > 0){//相同单元格数为不为0时进行合并

	                    			//first row(0-based) last row(0-based) first column(0-based) last column(0-based)
	                    			sheet.addMergedRegion(new CellRangeAddress(i-commonNum, i, 0, 0));//合并单元格
	                    			commonNum = 0;
	                    			if(commonNum2 > 0){//相同单元格数为不为0时进行合并
	                        			sheet.addMergedRegion(new CellRangeAddress(i-commonNum2, i, 1, 1));//合并单元格
	                        			commonNum2 = 0;
	                        		}
	                    		}
	                    	}
	                    	if((i == (protList.size() - 1)) && (commonNum > 0)){//最后一行且相同单元格数为不为0时进行合并
	                    		
	                    		sheet.addMergedRegion(new CellRangeAddress((i+1)-commonNum, i+1, 0, 0));//合并单元格
	                    		if(commonNum2 > 0){//相同单元格数为不为0时进行合并
	                    			sheet.addMergedRegion(new CellRangeAddress((i+1)-commonNum2, i+1, 1, 1));//合并单元格
	                    			commonNum2 = 0;
	                    		}
	                    	}
	                    }
	                }
	            }  
	        }
	        
	        try{
	        	
	        	storagePath = filePath + fileName + fileType;
	            FileOutputStream fout = new FileOutputStream(storagePath);//存储
	            workbook.write(fout);
	            fout.close();
	        }catch (Exception e) {
	        	
	            e.printStackTrace();
	        }finally{
	        	
	        	try {
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
	        }
	        
	        return storagePath;
		}
		
		/**
		 * 根据属性名称利用反射得到并执行属性所对应的get方法
		 * @param T class
		 * @param attribute 属性名
		 * @param entyObj entity
		 * @return
		 */
		public static Object obtainGetVal(Class<?> T, String attribute, Object entyObj){
			
			Class<?> enClass = T;
	        PropertyDescriptor prd;
	        Object obj = null;
			try {
				
				prd = new PropertyDescriptor(attribute, enClass);
				Method getMethod = prd.getReadMethod();
				obj = getMethod.invoke(entyObj);
			} catch (IntrospectionException | IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
				e.printStackTrace();
			}
			
			return obj;
		}
}
